package com.demo.my.Dao;

import com.demo.my.Entity.EmployInfoDTO;
import com.demo.my.Entity.Employee;
import com.demo.my.Util.BaseDAO;
import com.demo.my.Util.DBUtil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmployeeManagementDao extends BaseDAO {
    public List<EmployInfoDTO> getEmployee() throws Exception {
        String sql="select e.id,e.employee_number,e.name 'name',e.gender,e.telephone,e.in_time,d.name 'department_name',p.name 'position_name' from employee as e\n" +
                "left join department as d on e.department_number = d.department_number inner join position as p on e.position_number\n" +
                "= p.positionNumber";
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ArrayList<EmployInfoDTO> list = new ArrayList<>();
        while (rs.next()){
            int id = rs.getInt(1);
            int employee_number = rs.getInt(2);
            String name = rs.getString(3);
            String gender = rs.getString(4);
            String telephone = rs.getString(5);
            Date in_time = rs.getDate(6);
            String departmentName = rs.getString(7);
            String positionName = rs.getString(8);
            EmployInfoDTO employInfoDTO = new EmployInfoDTO(id, employee_number, name, gender, telephone, in_time, departmentName, positionName);
            list.add(employInfoDTO);
        }
        DBUtil.close(rs,ps,connection);
//      Object[]obj={};
//      return getList(sql,new EmployInfoDTO(),obj);
        return list;
    }

    public void addEmployee(Employee employee) throws Exception {
        String sql="insert into employee values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[]obj={employee.getEmployeeNumber(),employee.getName(),employee.getGender(),employee.getBirthday(),
        employee.getTelephone(),employee.getEmail(),employee.getAddress(),employee.getPhoto(),employee.getEducation(),employee.getDepartmentNumber(),
        employee.getPositionNumber(),employee.getInTime(),employee.getPassword(),employee.getNotes()};
        addOrupdOrdel(sql,obj);
    }

    public void deleteByid(String id) throws Exception {
        String sql="delete from employee where id=?";
        addOrupdOrdel(sql,id);
    }
}
